package j.util.jdbc;

import j.util.New;
import j.util.StringUtils;
import j.util.TwoTuple;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * User: jason
 * Date: 13-4-19 上午9:52
 */
public final class JdbcUtils {

    public static final Pattern NAMED_SQL_PARSER_PATTERN = Pattern.compile(":([a-zA-Z0-9]+)");

    /**
     * 解析namedSql语句为带？参数的sql语句，并返回sql及数组参数列表
     * @param namedSql
     * @param params
     * @return
     */
    public static TwoTuple<String,List<Object>> parseNamedSql(String namedSql,Map<String,Object> params){
        List<Object> args = New.list();
        Matcher m = NAMED_SQL_PARSER_PATTERN.matcher(namedSql);
        StringBuffer buffer = new StringBuffer();
        Object obj;
        String key;
        while(m.find()){
            key = m.group(1);
            if(!params.containsKey(key)){
                throw new RuntimeException("没有NamedSql["+namedSql+"]为指定参数["+key+"],请确认.");
            }
            obj = params.get(key);
            args.add(obj);
            m.appendReplacement(buffer, "?");
        }
        m.appendTail(buffer);
        return New.tuple(buffer.toString(),args);
    }
    public static TwoTuple<String,List<Object[]>> parseNamedSql(String namedSql,List<Map<String,Object>> params){
        Matcher m = NAMED_SQL_PARSER_PATTERN.matcher(namedSql);
        StringBuffer buffer = new StringBuffer();
        String key;
        List<String> keyList = New.list();
        while(m.find()){
            key = m.group(1);
            keyList.add(key);
            m.appendReplacement(buffer, "?");
        }
        m.appendTail(buffer);
        List<Object[]> argsList = New.list();
        //将ListMap类型的参数转换为数组类参数
        Object[] args;
        for(Map<String,Object> map : params){
            args = new Object[keyList.size()];
            for(int i=0;i<keyList.size();i++){
                key = keyList.get(i);
                if(!map.containsKey(key)){
                    throw new RuntimeException("没有NamedSql["+namedSql+"]为指定参数["+key+"],请确认.");
                }
                args[i] = map.get(key);
            }
            argsList.add(args);
        }
        return New.tuple(buffer.toString(),argsList);
    }

    public static void free(Connection conn,Statement stmt,ResultSet rs){
        if(rs != null){
            try{
                rs.close();
            }catch(SQLException ignored){}
        }
        if(stmt != null){
            try{
                stmt.close();
            }catch(SQLException ignored){}
        }
        if(conn!=null){
            try{
                conn.close();
            }catch(SQLException ignored){}
        }
    }

    public static void free(Connection conn,Statement stmt){
        if(stmt != null){
            try{
                stmt.close();
            }catch(SQLException ignored){}
        }
        if(conn!=null){
            try{
                if(!conn.isClosed()){
                    conn.close();
                }
            }catch(SQLException ignored){}
        }
    }

    public static void free(Connection conn){
        if(conn!=null){
            try{
                conn.close();
            }catch(SQLException ignored){}
        }
    }

    public static void setStatementArguments(PreparedStatement stmt,Object[] args){
        if(null!=args && args.length>0){
            try {
                for(int i=0;i<args.length;i++){
                    if(args[i] instanceof Date){
                        stmt.setTimestamp(i+1,new Timestamp(((Date)args[i]).getTime()));
                    }else{
                        stmt.setObject(i+1,args[i]);
                    }
                }
            } catch (SQLException e) {
                throw new RabbitJdbcException(String.format("执行数据库语句设置参数时异常，异常信息：%s.",e.getMessage()));
            }
        }
    }

    public static Object getResultSetValue_old(ResultSet rs, int index) throws SQLException {
        Object obj = rs.getObject(index);
        String className = null;
        if (obj != null) {
            className = obj.getClass().getName();
        }
        if (obj instanceof Blob) {
            obj = rs.getBytes(index);
        }
        else if (obj instanceof Clob) {
            obj = rs.getString(index);
        }
        else if (className != null &&
                ("oracle.sql.TIMESTAMP".equals(className) ||
                        "oracle.sql.TIMESTAMPTZ".equals(className))) {
            obj = rs.getTimestamp(index);
        }
        else if (className != null && className.startsWith("oracle.sql.DATE")) {
            String metaDataClassName = rs.getMetaData().getColumnClassName(index);
            if ("java.sql.Timestamp".equals(metaDataClassName) ||
                    "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
                obj = rs.getTimestamp(index);
            }
            else {
                obj = rs.getDate(index);
            }
        }
        else if (obj != null && obj instanceof Date) {
            if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
                obj = rs.getTimestamp(index);
            }
        }
        return obj;
    }
    public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
        Object obj = rs.getObject(index);
        String className = null;
        if (obj != null) {
            className = obj.getClass().getName();
        }
        if (obj instanceof Blob) {
            obj = rs.getBytes(index);
        }
        else if (obj instanceof Clob) {
            obj = rs.getString(index);
        }
        else if (className != null &&
                ("oracle.sql.TIMESTAMP".equals(className) ||
                        "oracle.sql.TIMESTAMPTZ".equals(className))) {
            obj = rs.getTimestamp(index);
        }
        else if (className != null && className.startsWith("oracle.sql.DATE")) {
            String metaDataClassName = rs.getMetaData().getColumnClassName(index);
            if ("java.sql.Timestamp".equals(metaDataClassName) ||
                    "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
                obj = rs.getTimestamp(index);
            }
            else {
                obj = rs.getDate(index);
            }
        }
        else if (obj != null && obj instanceof Date) {
            if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
                obj = rs.getTimestamp(index);
            }
        }
        return obj;
    }

    public static String formatSqlToCount(String sqlFetchRows,boolean isComplex){
        Pattern p ;
        if(isComplex){
            p = Pattern.compile("SELECT.+?@@FROM",Pattern.CASE_INSENSITIVE);
        }else{
            p = Pattern.compile("SELECT.+?FROM",Pattern.CASE_INSENSITIVE);
        }
        Matcher m = p.matcher(sqlFetchRows);
        StringBuffer sb = new StringBuffer();
        if(m.find()){
            m.appendReplacement(sb, "SELECT COUNT(*) FROM");
        }
        m.appendTail(sb);
        //将order by去掉
        String sql = sb.toString();
        int index = sql.toUpperCase().indexOf("ORDER BY");
        if(index>-1){
            sql = sql.substring(0,index);
        }
        return sql;
    }

    /**
     * 替换指定sql语句的select至from之间的部分为strReplace
     * @param sql
     * @param strReplace
     * @return
     */
    public static String replaceSqlSelect(String sql,String strReplace){
        boolean isComplex = sql.contains("@@");
        Pattern p ;
        if(isComplex){
            p = Pattern.compile("SELECT.+?@@FROM",Pattern.CASE_INSENSITIVE);
        }else{
            p = Pattern.compile("SELECT.+?FROM",Pattern.CASE_INSENSITIVE);
        }
        Matcher m = p.matcher(sql);
        StringBuffer sb = new StringBuffer();
        if(m.find()){
            m.appendReplacement(sb, "SELECT "+strReplace+" FROM");
        }
        m.appendTail(sb);
        //将order by去掉
        sql = sb.toString();
        int index = sql.toUpperCase().indexOf("ORDER BY");
        if(index>-1){
            sql = sql.substring(0,index);
        }
        return sql;
    }

    public static Date getDateFromResultSet(ResultSet rs,String key) throws SQLException{
        Timestamp timestamp = rs.getTimestamp(key);
        if(null == timestamp){
            return null;
        }
        return new Date(timestamp.getTime());
    }

    public static Integer getInteger(ResultSet rs,String key) throws SQLException{
        Object o = rs.getObject(key);
        if(null!=o && (o instanceof Integer
                        || o instanceof Short)){
            return Integer.valueOf(String.valueOf(o));
        }
        return null;
    }
    public static Boolean getBoolean(ResultSet rs,String key) throws SQLException{
        Object o = rs.getObject(key);
        if(null!=o){
            if(o instanceof Integer){
                int i = (Integer)o;
                return i>0;
            }else if(o instanceof Short){
                short s = (Short)o;
                return s>0;
            }
        }
        return null;
    }
    public static Float getFloat(ResultSet rs,String key) throws SQLException{
        Object o = rs.getObject(key);
        if(null!=o && o instanceof Float){
            return (Float)o;
        }else if(o instanceof BigDecimal){
            BigDecimal bd = (BigDecimal)o;
            return bd.floatValue();
        }
        return null;
    }
    /**
     * 获取float类型值，若为空，则返回0
     * @param rs
     * @param key
     * @return
     * @throws java.sql.SQLException
     */
    public static float getFloatValue(ResultSet rs,String key) throws SQLException{
        Float f = getFloat(rs, key);
        if(f == null){
            return 0;
        }
        return f;
    }
    public static Double getDouble(ResultSet rs,String key) throws SQLException{
        Object o = rs.getObject(key);
        if(null!=o && o instanceof Double){
            return (Double)o;
        }else if(o instanceof BigDecimal){
            BigDecimal bd = (BigDecimal)o;
            return bd.doubleValue();
        }
        return null;
    }

    public static Double getMsSqlDouble(ResultSet rs,String key)throws SQLException{
        BigDecimal bd = rs.getBigDecimal(key);
        if(bd!=null){
            return bd.doubleValue();
        }
        return null;
    }

    /**
     * 追加sql查询的and，若给定的语句大于0，则追加and，否则不追加任何内容
     * @param sql
     */
    public static StringBuilder appendAnd(StringBuilder sql){
        if(sql.length()>0){
            sql.append(" AND ");
        }
        return sql;
    }
}
